# -*- coding: utf-8 -*-
from apps import configs
from apps.utils import Utils


class DeviceModel(object):
    def __init__(self, db):
        self.db = db
        self.table_name = '%sdevice' % configs.TABLE_NAME_PREFIX

    async def add_new_device(self, uuid, status, sn, name, offline, user_id):
        sql = 'INSERT INTO %s' % self.table_name
        sql += '(uuid,status,sn,name,offline,user_id) VALUES(%s,%s,%s,%s,%s,%s)'
        return await self.db.execute(
            sql, (uuid, status, sn, name, offline, user_id))

    async def get_device_by_sn(self, sn):
        sql = 'SELECT * FROM %s ' % self.table_name
        sql += 'WHERE sn=%s'
        return await self.db.fetchone(sql, sn)

    async def get_device_by_uuid(self, uuid):
        sql = 'SELECT * FROM %s ' % self.table_name
        sql += 'WHERE uuid=%s'
        return await self.db.fetchone(sql, uuid)

    async def get_devices_by_userid(self, userid):
        sql = 'SELECT * FROM %s ' % self.table_name
        sql += 'WHERE user_id=%s'
        return await self.db.fetchall(sql, userid)

    async def get_device_by_uuid_and_userid(self, uuid, userid):
        sql = 'SELECT * FROM %s ' % self.table_name
        sql += 'WHERE uuid=%s AND user_id=%s'
        return await self.db.fetchone(sql, (uuid, userid))

    async def del_device_by_uuid(self, uuid):
        sql = 'DELETE FROM %s ' % self.table_name
        sql += 'WHERE uuid=%s'
        return await self.db.execute(sql, uuid)

    async def set_device_status_by_uuid(self,
                                        uuid,
                                        status,
                                        offline,
                                        online_time=None):

        sql = 'UPDATE %s SET status=%s,offline=%s' % (self.table_name, status,
                                                      offline)
        if online_time:
            sql += ',online_time=%s' % online_time
        sql += ' WHERE uuid = %s' % uuid
        return await self.db.execute(sql)

    async def get_devices(self,
                          userid,
                          page: int = 1,
                          limit: int = 15,
                          offline: int = 0,
                          query=''):
        page = (page - 1) * limit
        where = 'WHERE user_id=%s' % userid

        if offline != 0:
            where += ' AND offline=%s' % offline
        if query:
            where += ' AND (sn="%s" OR name="%s")' % (query, query)

        sql = 'SELECT * FROM %s AS t JOIN (SELECT id FROM %s %s ORDER BY add_time DESC LIMIT %s, %s) tmp ON t.id = ' \
              'tmp.id' % (
                  self.table_name, self.table_name, where, page, limit)

        return await self.db.fetchall(sql)
